{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "e7ccec34-49d0-4376-9d1f-c4d16162d638",
   "metadata": {},
   "outputs": [],
   "source": [
    "import os, math\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "import matplotlib.pyplot as plt\n",
    "from sklearn.metrics import roc_curve, auc\n",
    "from sklearn.metrics import precision_recall_curve, average_precision_score\n",
    "import matplotlib.pyplot as plt\n",
    "import seaborn as snszzzz\n",
    "import helperFunctions\n",
    "from collections import Counter\n",
    "from sklearn.model_selection import train_test_split"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "35e7fb2a-1896-43a8-abe5-191f65f2e17e",
   "metadata": {},
   "source": [
    "Download data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "id": "3386b833-2099-44fa-9e29-8726a45d5941",
   "metadata": {},
   "outputs": [],
   "source": [
    "# %% Build a list of league and season identifiers\n",
    "\n",
    "# build a list of league identifiers as specified in www.football-data.co.uk data\n",
    "leagueNames = [\"D1\", \"SP1\", \"F1\", \"E0\", \"I1\", \"B1\", \"E0\", \"E1\", \"E2\", \"E3\", \"F1\", \"F2\", \"G1\", \"N1\"]\n",
    "\n",
    "# build a list of season identifiers i.e. 0809 for 2008-09 season, 0910 for 2009-10 season,\n",
    "seasonIdentifiers = [\n",
    "    str(seasonStartYear)[-2:] + str(seasonStartYear + 1)[-2:]\n",
    "    for seasonStartYear in range(2022, 2023 + 1)\n",
    "]\n",
    "\n",
    "# %% download CSVs from football-data.co.uk and store the data. Notes about the same available in https://www.football-data.co.uk/notes.txt\n",
    "\n",
    "if not os.path.exists(\"downloaded_data\"):\n",
    "    os.mkdir(\"downloaded_data\")  # create directory to save the CSVs\n",
    "\n",
    "for leagueIx, leagueId in enumerate(leagueNames):\n",
    "    for seasonIx, seasonId in enumerate(seasonIdentifiers):\n",
    "        # the URL to access information is the following format\n",
    "        link = f\"https://www.football-data.co.uk/mmz4281/{seasonId}/{leagueId}.csv\"\n",
    "        saveLocation = os.path.join(\n",
    "            \"downloaded_data\", f\"{leagueNames[leagueIx]}_{seasonId}.csv\"\n",
    "        )\n",
    "        pd.read_csv(link).to_csv(\n",
    "            saveLocation, index=False\n",
    "        )  # read from the link and save it as CSV"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "62e3ddfb",
   "metadata": {},
   "outputs": [],
   "source": [
    "# %% Build a list of league and season identifiers\n",
    "\n",
    "# build a list of league identifiers as specified in www.football-data.co.uk data\n",
    "leagueNames = [\"D1\", \"SP1\", \"F1\", \"E0\", \"I1\", \"B1\", \"E0\", \"E1\", \"E2\", \"E3\", \"F1\", \"F2\", \"G1\", \"N1\"]\n",
    "\n",
    "# build a list of season identifiers i.e. 0809 for 2008-09 season, 0910 for 2009-10 season,\n",
    "seasonIdentifiers = [\n",
    "    str(seasonStartYear)[-2:] + str(seasonStartYear + 1)[-2:]\n",
    "    for seasonStartYear in range(2020, 2022)\n",
    "]\n",
    "\n",
    "# %% download CSVs from football-data.co.uk and store the data. Notes about the same available in https://www.football-data.co.uk/notes.txt\n",
    "\n",
    "if not os.path.exists(\"downloaded_previous_data\"):\n",
    "    os.mkdir(\"downloaded_previous_data\")  # create directory to save the CSVs\n",
    "\n",
    "for leagueIx, leagueId in enumerate(leagueNames):\n",
    "    for seasonIx, seasonId in enumerate(seasonIdentifiers):\n",
    "        # the URL to access information is the following format\n",
    "        link = f\"https://www.football-data.co.uk/mmz4281/{seasonId}/{leagueId}.csv\"\n",
    "        saveLocation = os.path.join(\n",
    "            \"downloaded_previous_data\", f\"{leagueNames[leagueIx]}_{seasonId}.csv\"\n",
    "        )\n",
    "        pd.read_csv(link).to_csv(\n",
    "            saveLocation, index=False\n",
    "        )  # read from the link and save it as CSV"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "id": "b91e8f20-35f4-4daf-a174-dff947561e47",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>AC</th>\n",
       "      <th>AF</th>\n",
       "      <th>AR</th>\n",
       "      <th>AS</th>\n",
       "      <th>AST</th>\n",
       "      <th>AY</th>\n",
       "      <th>AwayTeam</th>\n",
       "      <th>Date</th>\n",
       "      <th>FTR</th>\n",
       "      <th>HC</th>\n",
       "      <th>HF</th>\n",
       "      <th>HR</th>\n",
       "      <th>HS</th>\n",
       "      <th>HST</th>\n",
       "      <th>HTAG</th>\n",
       "      <th>HTHG</th>\n",
       "      <th>HY</th>\n",
       "      <th>HomeTeam</th>\n",
       "      <th>Div</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>4.0</td>\n",
       "      <td>18.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>Verona</td>\n",
       "      <td>19/08/2023</td>\n",
       "      <td>A</td>\n",
       "      <td>2.0</td>\n",
       "      <td>17.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>Empoli</td>\n",
       "      <td>I1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>6.0</td>\n",
       "      <td>17.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>19.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>Napoli</td>\n",
       "      <td>19/08/2023</td>\n",
       "      <td>A</td>\n",
       "      <td>4.0</td>\n",
       "      <td>14.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>Frosinone</td>\n",
       "      <td>I1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>4.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>Fiorentina</td>\n",
       "      <td>19/08/2023</td>\n",
       "      <td>A</td>\n",
       "      <td>3.0</td>\n",
       "      <td>14.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>Genoa</td>\n",
       "      <td>I1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>Monza</td>\n",
       "      <td>19/08/2023</td>\n",
       "      <td>H</td>\n",
       "      <td>8.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>22.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>Inter</td>\n",
       "      <td>I1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>Salernitana</td>\n",
       "      <td>20/08/2023</td>\n",
       "      <td>D</td>\n",
       "      <td>9.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Roma</td>\n",
       "      <td>I1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    AC    AF   AR    AS  AST   AY     AwayTeam        Date FTR   HC    HF  \\\n",
       "0  4.0  18.0  0.0  10.0  4.0  2.0       Verona  19/08/2023   A  2.0  17.0   \n",
       "1  6.0  17.0  0.0  19.0  8.0  3.0       Napoli  19/08/2023   A  4.0  14.0   \n",
       "2  4.0  13.0  0.0   9.0  5.0  3.0   Fiorentina  19/08/2023   A  3.0  14.0   \n",
       "3  3.0  13.0  0.0  12.0  2.0  1.0        Monza  19/08/2023   H  8.0   8.0   \n",
       "4  1.0   9.0  0.0   3.0  2.0  4.0  Salernitana  20/08/2023   D  9.0  12.0   \n",
       "\n",
       "    HR    HS  HST  HTAG  HTHG   HY   HomeTeam Div  \n",
       "0  0.0  10.0  4.0   0.0   0.0  2.0     Empoli  I1  \n",
       "1  0.0   4.0  1.0   2.0   1.0  3.0  Frosinone  I1  \n",
       "2  0.0   4.0  2.0   3.0   0.0  2.0      Genoa  I1  \n",
       "3  0.0  22.0  3.0   0.0   1.0  1.0      Inter  I1  \n",
       "4  0.0  13.0  3.0   1.0   1.0  0.0       Roma  I1  "
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Directory containing CSV files\n",
    "directory_path = \"./downloaded_data\"\n",
    "\n",
    "# List all CSV files in the directory\n",
    "csv_files = [file for file in os.listdir(directory_path) if file.endswith('.csv')]\n",
    "\n",
    "# Initialize an empty list to store DataFrames\n",
    "dfs = []\n",
    "\n",
    "# Loop through each CSV file, read it, and append to dfs list\n",
    "for file in csv_files:\n",
    "    file_path = os.path.join(directory_path, file)\n",
    "    dfs.append(pd.read_csv(file_path))\n",
    "\n",
    "# Concatenate all DataFrames in the list\n",
    "df = pd.concat(dfs, ignore_index=True)\n",
    "\n",
    "selected_columns = ['AC', 'AF', 'AR', 'AS', 'AST', 'AY', 'AwayTeam', 'Date', 'FTR', 'HC', 'HF', 'HR', 'HS', 'HST', 'HTAG', 'HTHG', 'HY', 'HomeTeam', 'Div']\n",
    "df = df[selected_columns]\n",
    "df['league'] = df['Div']\n",
    "# Drop the 'Div' column\n",
    "df.drop(columns=['Div'], inplace=True)\n",
    "# Display the concatenated DataFrame\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "id": "60fc11ab-141e-49e4-a3a3-8e14c78c3dc6",
   "metadata": {},
   "outputs": [],
   "source": [
    "if not os.path.exists('original_data'):\n",
    "    os.mkdir(\"original_data\") # create directory to save the CSVs\n",
    "\n",
    "if not os.path.exists('original_data'):\n",
    "    os.mkdir(\"original_data\") # create directory to save the CSVs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "id": "d704e8de-6bf6-40d8-9e3e-f167df376124",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Splitting the dataframe into train and test sets\n",
    "\n",
    "train_df, test_df = train_test_split(df, test_size=0.2, random_state=42)\n",
    "\n",
    "# Define the directory to save the files\n",
    "directory = \"original_data\"\n",
    "\n",
    "# Check if the directory exists, if not, create it\n",
    "if not os.path.exists(directory):\n",
    "    os.makedirs(directory)\n",
    "\n",
    "# Save train and test data to CSV files\n",
    "train_df.to_csv(os.path.join(directory, \"train.csv\"), index=False)\n",
    "test_df.to_csv(os.path.join(directory, \"test-3.csv\"), index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "id": "6ec6e15b-6feb-4e1c-8117-1d2bd148281a",
   "metadata": {},
   "outputs": [],
   "source": [
    "# read the train and test csv files\n",
    "train_df = pd.read_csv('original_data/train.csv')\n",
    "test_df = pd.read_csv('original_data/test-3.csv') # no need to read the index column"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "id": "5bb3ddbc-f4b7-40e0-8575-ca6a8daf7f44",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>AC</th>\n",
       "      <th>AF</th>\n",
       "      <th>AR</th>\n",
       "      <th>AS</th>\n",
       "      <th>AST</th>\n",
       "      <th>AY</th>\n",
       "      <th>AwayTeam</th>\n",
       "      <th>Date</th>\n",
       "      <th>FTR</th>\n",
       "      <th>HC</th>\n",
       "      <th>HF</th>\n",
       "      <th>HR</th>\n",
       "      <th>HS</th>\n",
       "      <th>HST</th>\n",
       "      <th>HTAG</th>\n",
       "      <th>HTHG</th>\n",
       "      <th>HY</th>\n",
       "      <th>HomeTeam</th>\n",
       "      <th>league</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>3.0</td>\n",
       "      <td>15.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>14.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>Arsenal</td>\n",
       "      <td>15/01/2023</td>\n",
       "      <td>A</td>\n",
       "      <td>4.0</td>\n",
       "      <td>16.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>17.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>Tottenham</td>\n",
       "      <td>E0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>6.0</td>\n",
       "      <td>16.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>Almere City</td>\n",
       "      <td>04/04/2024</td>\n",
       "      <td>D</td>\n",
       "      <td>5.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>15.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>Waalwijk</td>\n",
       "      <td>N1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>6.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>Sheffield United</td>\n",
       "      <td>25/02/2024</td>\n",
       "      <td>H</td>\n",
       "      <td>5.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>Wolves</td>\n",
       "      <td>E0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2.0</td>\n",
       "      <td>14.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>Annecy</td>\n",
       "      <td>25/02/2023</td>\n",
       "      <td>H</td>\n",
       "      <td>2.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Sochaux</td>\n",
       "      <td>F2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>6.0</td>\n",
       "      <td>15.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>Panathinaikos</td>\n",
       "      <td>28/01/2024</td>\n",
       "      <td>H</td>\n",
       "      <td>3.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>PAOK</td>\n",
       "      <td>G1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    AC    AF   AR    AS  AST   AY          AwayTeam        Date FTR   HC  \\\n",
       "0  3.0  15.0  0.0  14.0  5.0  2.0           Arsenal  15/01/2023   A  4.0   \n",
       "1  6.0  16.0  0.0   9.0  0.0  1.0       Almere City  04/04/2024   D  5.0   \n",
       "2  6.0   4.0  0.0  12.0  4.0  1.0  Sheffield United  25/02/2024   H  5.0   \n",
       "3  2.0  14.0  0.0   7.0  2.0  1.0            Annecy  25/02/2023   H  2.0   \n",
       "4  6.0  15.0  0.0  10.0  5.0  2.0     Panathinaikos  28/01/2024   H  3.0   \n",
       "\n",
       "     HF   HR    HS  HST  HTAG  HTHG   HY   HomeTeam league  \n",
       "0  16.0  0.0  17.0  7.0   2.0   0.0  4.0  Tottenham     E0  \n",
       "1   7.0  0.0  15.0  3.0   0.0   0.0  2.0   Waalwijk     N1  \n",
       "2   8.0  0.0  13.0  2.0   0.0   1.0  2.0     Wolves     E0  \n",
       "3   8.0  0.0  11.0  7.0   0.0   3.0  0.0    Sochaux     F2  \n",
       "4   9.0  0.0   9.0  4.0   0.0   1.0  4.0       PAOK     G1  "
      ]
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "train_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "id": "5d25a9a9-7d40-4b01-8e2f-ebf04f278b48",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['AC', 'AF', 'AR', 'AS', 'AST', 'AY', 'AwayTeam', 'Date', 'FTR', 'HC', 'HF', 'HR', 'HS', 'HST', 'HTAG', 'HTHG', 'HY', 'HomeTeam', 'league'] i.e. 19 columns are common between train_df and test_df\n"
     ]
    }
   ],
   "source": [
    "commonColumns = set(list(train_df.columns)).intersection(set(list(test_df.columns))) # between train and test dataframes\n",
    "print(sorted(commonColumns), f\"i.e. {len(commonColumns)} columns are common between train_df and test_df\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "id": "64fed494-753c-4733-9f71-63dcd45e81ab",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "set()"
      ]
     },
     "execution_count": 50,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "set(list(train_df.columns)) - commonColumns  # columns only in train_df\n",
    "set(list(test_df.columns)) - commonColumns  # columns only in test_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "id": "591f37a2-2832-4535-a774-e1fe6c9cc60d",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Change string column to datetime64 column\n",
    "train_df[\"Date\"] = pd.to_datetime(train_df[\"Date\"], format=\"%d/%m/%Y\")\n",
    "test_df[\"Date\"] = pd.to_datetime(test_df[\"Date\"], format=\"%d/%m/%Y\")\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "id": "22c7259e-da76-4e07-a3b1-0ce04601fe7e",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "Oldest date in train_df is 23 July 2022\n",
      "Latest date in train_df is 22 April 2024\n",
      "\n",
      "Oldest date in test_df is 22 July 2022\n",
      "Latest date in test_df is 21 April 2024\n"
     ]
    }
   ],
   "source": [
    "print(f\"\\nOldest date in train_df is {min(train_df['Date']).strftime('%d %B %Y')}\")\n",
    "print(f\"Latest date in train_df is {max(train_df['Date']).strftime('%d %B %Y')}\")\n",
    "\n",
    "print(f\"\\nOldest date in test_df is {min(test_df['Date']).strftime('%d %B %Y')}\")\n",
    "print(f\"Latest date in test_df is {max(test_df['Date']).strftime('%d %B %Y')}\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "id": "00e5654f-3d47-4b30-a65d-fb4f043b431e",
   "metadata": {},
   "outputs": [],
   "source": [
    "# the following columns are always non-negative integers and can never be float values. The values are always less than the max value of Int8 since there can't something like 127 (max value of signed int) corners/shots in a match. Let us change them to integer values. Null values are handled automatically for us.\n",
    "\n",
    "columnNames = ['AC', 'AF', 'AR', 'AS', 'AST', 'AY', 'HC', 'HF', 'HR', 'HS', 'HST', 'HTAG', 'HTHG', 'HY']\n",
    "for columnName in columnNames:\n",
    "    if min(train_df[columnName]) < 0 or min(test_df[columnName]) < 0:\n",
    "        raise Exception(f\"Value under {columnName} in train_df or test_df is negative. This cannot happen. Some issue with the data!!\") # not handling this exception since I want the code to stop running on seeing this issue.\n",
    "    # if no issue found, change that column to integer type.\n",
    "    train_df[columnName] = train_df[columnName].astype('Int8')\n",
    "    test_df[columnName] = test_df[columnName].astype('Int8')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "id": "18bec84e-b859-43e1-b83c-5ac3cb6cf811",
   "metadata": {},
   "outputs": [],
   "source": [
    "# the following columns are/should always be strings\n",
    "# skipped Referee since we will dropping it. FTR will be looked into.\n",
    "columnNames = ['AwayTeam', 'HomeTeam', 'league']\n",
    "for columnName in columnNames:\n",
    "    train_df[columnName] = train_df[columnName].astype('string')\n",
    "    test_df[columnName] = test_df[columnName].astype('string')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "id": "2f3dc006-352b-4a6d-ae5a-cd2ddc27aeb0",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Counter({'H': 3102, 'A': 2219, 'D': 1812})"
      ]
     },
     "execution_count": 55,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# check for any unexpected values under the FTR column\n",
    "Counter(list(train_df[\"FTR\"].astype('string')))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "id": "761a8631-e825-4195-bf27-c75882e03246",
   "metadata": {},
   "outputs": [],
   "source": [
    "# change FTR column to string.\n",
    "train_df[\"FTR\"] = train_df[\"FTR\"].astype('string') # null values are taken care of automatically"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "id": "cc579204-40f5-416b-add5-aa5e510b0aab",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Number of rows in train_df = 7133\n",
      "Number of rows in test_df = 1784\n",
      "After dropping duplicates ...\n",
      "Number of rows in train_df = 7133\n",
      "Number of rows in test_df = 1784\n"
     ]
    }
   ],
   "source": [
    "# check for duplicated rows and keep only one occurence of the row. We'll choose the first occurence of the row. \n",
    "print(f\"Number of rows in train_df = {len(train_df)}\")\n",
    "print(f\"Number of rows in test_df = {len(test_df)}\")\n",
    "\n",
    "# drop duplicates\n",
    "train_df = train_df.drop_duplicates(keep='first')\n",
    "test_df = test_df.drop_duplicates(keep='first')\n",
    "print(\"After dropping duplicates ...\")\n",
    "\n",
    "print(f\"Number of rows in train_df = {len(train_df)}\")\n",
    "print(f\"Number of rows in test_df = {len(test_df)}\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "id": "af83001b-eb7a-4923-ae65-85bb5387b8d8",
   "metadata": {},
   "outputs": [],
   "source": [
    "# let us drop the Referee column in the test_df since 1) we do not have it while training 2) referee should/does not influence the game.\n",
    "#test_df = test_df.drop(['Referee'], axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "id": "2b765ae0-188d-4909-9729-a1fb3390add4",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "AC          2\n",
       "AF          2\n",
       "AR          2\n",
       "AS          2\n",
       "AST         2\n",
       "AY          2\n",
       "AwayTeam    0\n",
       "Date        0\n",
       "FTR         0\n",
       "HC          2\n",
       "HF          2\n",
       "HR          2\n",
       "HS          2\n",
       "HST         2\n",
       "HTAG        2\n",
       "HTHG        2\n",
       "HY          2\n",
       "HomeTeam    0\n",
       "league      0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 59,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "train_df.isnull().sum() # number of null values by column in train_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "id": "39be49ba-63d1-4351-8311-d89d388c4383",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "AC          0\n",
       "AF          0\n",
       "AR          0\n",
       "AS          0\n",
       "AST         0\n",
       "AY          0\n",
       "AwayTeam    0\n",
       "Date        0\n",
       "FTR         0\n",
       "HC          0\n",
       "HF          0\n",
       "HR          0\n",
       "HS          0\n",
       "HST         0\n",
       "HTAG        0\n",
       "HTHG        0\n",
       "HY          0\n",
       "HomeTeam    0\n",
       "league      0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 60,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "test_df.isnull().sum() # number of null values by column in test_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "id": "87acd0d0-31d1-4615-b8ce-893ac68cc6e5",
   "metadata": {},
   "outputs": [],
   "source": [
    "train_df[train_df.isnull().any(axis=1)]\n",
    "#remove rows with null values\n",
    "train_df = train_df.dropna()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "id": "865ce830-6d59-45ed-a185-be1ace1df508",
   "metadata": {},
   "outputs": [],
   "source": [
    "#train_df = train_df.drop(axis=1, index = [7082, 9363, 11644])  # drop from train_df where all columns are null values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "id": "3ec44011-c184-455b-ac1c-0336d649e53a",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>AC</th>\n",
       "      <th>AF</th>\n",
       "      <th>AR</th>\n",
       "      <th>AS</th>\n",
       "      <th>AST</th>\n",
       "      <th>AY</th>\n",
       "      <th>AwayTeam</th>\n",
       "      <th>Date</th>\n",
       "      <th>FTR</th>\n",
       "      <th>HC</th>\n",
       "      <th>HF</th>\n",
       "      <th>HR</th>\n",
       "      <th>HS</th>\n",
       "      <th>HST</th>\n",
       "      <th>HTAG</th>\n",
       "      <th>HTHG</th>\n",
       "      <th>HY</th>\n",
       "      <th>HomeTeam</th>\n",
       "      <th>league</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [AC, AF, AR, AS, AST, AY, AwayTeam, Date, FTR, HC, HF, HR, HS, HST, HTAG, HTHG, HY, HomeTeam, league]\n",
       "Index: []"
      ]
     },
     "execution_count": 63,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "test_df[test_df.isnull().any(axis=1)] # check rows with one or more null values again in test_df\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "id": "cb7e28b0-938a-4300-8936-433641b0ed66",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Leagues in train_df :: ['B1', 'D1', 'E0', 'E1', 'E2', 'E3', 'F1', 'F2', 'G1', 'I1', 'N1', 'SP1']\n",
      "Leagues in test_df ::  ['B1', 'D1', 'E0', 'E1', 'E2', 'E3', 'F1', 'F2', 'G1', 'I1', 'N1', 'SP1']\n"
     ]
    }
   ],
   "source": [
    "# list the leagues in both the dfs\n",
    "print(\"Leagues in train_df ::\", list(np.unique(train_df[\"league\"])))\n",
    "print(\"Leagues in test_df :: \", list(np.unique(test_df[\"league\"])))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "id": "e5024021-b1b5-4415-befb-b075779942e6",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>AC</th>\n",
       "      <th>AF</th>\n",
       "      <th>AR</th>\n",
       "      <th>AS</th>\n",
       "      <th>AST</th>\n",
       "      <th>AY</th>\n",
       "      <th>AwayTeam</th>\n",
       "      <th>Date</th>\n",
       "      <th>FTR</th>\n",
       "      <th>HC</th>\n",
       "      <th>HF</th>\n",
       "      <th>HR</th>\n",
       "      <th>HS</th>\n",
       "      <th>HST</th>\n",
       "      <th>HTAG</th>\n",
       "      <th>HTHG</th>\n",
       "      <th>HY</th>\n",
       "      <th>HomeTeam</th>\n",
       "      <th>league</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [AC, AF, AR, AS, AST, AY, AwayTeam, Date, FTR, HC, HF, HR, HS, HST, HTAG, HTHG, HY, HomeTeam, league]\n",
       "Index: []"
      ]
     },
     "execution_count": 65,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# identify rows in train_df that have one or more null rows\n",
    "train_df_oneOrMoreNulls = train_df[train_df.isnull().any(axis=1)]\n",
    "train_df_oneOrMoreNulls"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "id": "ba78660f-9b74-4d35-bd7b-7acbf547d94a",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\u001b[0;31mSignature:\u001b[0m\n",
      " \u001b[0mhelperFunctions\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mextractMissingMatchStatsForCurrentMatch\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m\u001b[0m\n",
      "\u001b[0;34m\u001b[0m    \u001b[0mmissingFields\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\n",
      "\u001b[0;34m\u001b[0m    \u001b[0mrow\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\n",
      "\u001b[0;34m\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;31mDocstring:\u001b[0m\n",
      "Extract missing value for the specified field after searching for the match.\n",
      "We do this to just \"get\" the value the training data might be missing and is available in downloaded_data\n",
      "\u001b[0;31mFile:\u001b[0m      ~/Documents/Projects/model_todo/helperFunctions.py\n",
      "\u001b[0;31mType:\u001b[0m      function"
     ]
    }
   ],
   "source": [
    "? helperFunctions.extractMissingMatchStatsForCurrentMatch"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "id": "88175713-c9ad-43d3-a4b4-27cdbae2c981",
   "metadata": {},
   "outputs": [],
   "source": [
    "for rowIx, row in train_df_oneOrMoreNulls.iterrows():\n",
    "    missingFields = list(row.index[row.isnull()])\n",
    "    df = helperFunctions.extractMissingMatchStatsForCurrentMatch([\"Date\", \"HomeTeam\", \"AwayTeam\"] + missingFields, row)\n",
    "    df = df.assign(league=row['league']) # add new column for easier reading\n",
    "    print(df)\n",
    "    print(\"--------\"*9,)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "id": "6ec8ac6b-d711-4770-b1cf-a41b8ef0737b",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\u001b[0;31mSignature:\u001b[0m  \u001b[0mhelperFunctions\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0maccessValuesUnderThisFieldForThisSeason\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mfield\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mrow\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;31mDocstring:\u001b[0m Access all values under the specified field for the team that season. These can be used impute the missing values.\n",
      "\u001b[0;31mFile:\u001b[0m      ~/Documents/Projects/model_todo/helperFunctions.py\n",
      "\u001b[0;31mType:\u001b[0m      function"
     ]
    }
   ],
   "source": [
    "? helperFunctions.accessValuesUnderThisFieldForThisSeason"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "id": "3afcaef2-804d-4e4f-a396-59f773e26dad",
   "metadata": {},
   "outputs": [],
   "source": [
    "# view values seen in the season for the chosen home/away team (19 values for 19 matches) and print the mean and median value as a sanity check\n",
    "\n",
    "for rowIx, row in train_df_oneOrMoreNulls.iterrows():\n",
    "    missingFields = list(row.index[row.isnull()])\n",
    "    print(\"%\"*10, f\"{row['league']} match :: {row['HomeTeam']} vs. {row['AwayTeam']} on {row['Date'].strftime('%d %B %Y')} -- {len(missingFields)} null columns\", \"%\"*10)\n",
    "    \n",
    "    for ix, missingField in enumerate(missingFields):\n",
    "        missingFieldValuesForCurrentSeason = helperFunctions.accessValuesUnderThisFieldForThisSeason(missingField, row)\n",
    "        print(missingField + f\"\\tmean = {np.round(np.nanmean(list(missingFieldValuesForCurrentSeason)), 3)}\" + f\"\\tmedian = {np.round(np.nanmedian(list(missingFieldValuesForCurrentSeason)), 3)}\")\n",
    "        print(list(missingFieldValuesForCurrentSeason))\n",
    "        print(\"\\n\\n\") if ix+1 == len(missingFields) else print(\"-------\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "id": "fe9037f5-baea-40cf-a570-2371c3200a10",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\u001b[0;31mSignature:\u001b[0m  \u001b[0mhelperFunctions\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0maccessValuesUnderThisFieldForThisSeason\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mfield\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mrow\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;31mDocstring:\u001b[0m Access all values under the specified field for the team that season. These can be used impute the missing values.\n",
      "\u001b[0;31mFile:\u001b[0m      ~/Documents/Projects/model_todo/helperFunctions.py\n",
      "\u001b[0;31mType:\u001b[0m      function"
     ]
    }
   ],
   "source": [
    "? helperFunctions.accessValuesUnderThisFieldForThisSeason"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "id": "00233c65-cc38-4eb6-ae8e-fd2260e46194",
   "metadata": {},
   "outputs": [],
   "source": [
    "# impute missing values in train_df_oneOrMoreNulls to check if it's producing desired results\n",
    "\n",
    "for rowIx, row in train_df_oneOrMoreNulls.iterrows():\n",
    "    missingFields = list(row.index[row.isnull()])\n",
    "\n",
    "    for missingField in missingFields:\n",
    "        valuesForCurrentSeason = helperFunctions.accessValuesUnderThisFieldForThisSeason(missingField, row)\n",
    "        # we will round the median values, make them of integer type and then impute those values in the dataframe\n",
    "        train_df_oneOrMoreNulls.at[rowIx, missingField] = int(np.round(np.nanmedian(list(valuesForCurrentSeason)), 0))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "id": "b4e2fe72-8d26-4585-bfc0-f24e5355eddc",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>AC</th>\n",
       "      <th>AF</th>\n",
       "      <th>AR</th>\n",
       "      <th>AS</th>\n",
       "      <th>AST</th>\n",
       "      <th>AY</th>\n",
       "      <th>AwayTeam</th>\n",
       "      <th>Date</th>\n",
       "      <th>FTR</th>\n",
       "      <th>HC</th>\n",
       "      <th>HF</th>\n",
       "      <th>HR</th>\n",
       "      <th>HS</th>\n",
       "      <th>HST</th>\n",
       "      <th>HTAG</th>\n",
       "      <th>HTHG</th>\n",
       "      <th>HY</th>\n",
       "      <th>HomeTeam</th>\n",
       "      <th>league</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [AC, AF, AR, AS, AST, AY, AwayTeam, Date, FTR, HC, HF, HR, HS, HST, HTAG, HTHG, HY, HomeTeam, league]\n",
       "Index: []"
      ]
     },
     "execution_count": 72,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "train_df_oneOrMoreNulls"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "id": "6fb00a7a-a48a-4ad0-b5f6-bd3726a5443b",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>AC</th>\n",
       "      <th>AF</th>\n",
       "      <th>AR</th>\n",
       "      <th>AS</th>\n",
       "      <th>AST</th>\n",
       "      <th>AY</th>\n",
       "      <th>AwayTeam</th>\n",
       "      <th>Date</th>\n",
       "      <th>FTR</th>\n",
       "      <th>HC</th>\n",
       "      <th>HF</th>\n",
       "      <th>HR</th>\n",
       "      <th>HS</th>\n",
       "      <th>HST</th>\n",
       "      <th>HTAG</th>\n",
       "      <th>HTHG</th>\n",
       "      <th>HY</th>\n",
       "      <th>HomeTeam</th>\n",
       "      <th>league</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [AC, AF, AR, AS, AST, AY, AwayTeam, Date, FTR, HC, HF, HR, HS, HST, HTAG, HTHG, HY, HomeTeam, league]\n",
       "Index: []"
      ]
     },
     "execution_count": 73,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "train_df[train_df.isnull().any(axis=1)] # viewing null values of train_df before changing them"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "id": "47301f05-950d-4213-ad7c-a78e3c720d63",
   "metadata": {},
   "outputs": [],
   "source": [
    "# impute missing values in train_df_oneOrMoreNulls to check if it's producing desired results\n",
    "\n",
    "for rowIx, row in train_df.iterrows():\n",
    "    missingFields = list(row.index[row.isnull()])\n",
    "\n",
    "    for missingField in missingFields:\n",
    "        valuesForCurrentSeason = helperFunctions.accessValuesUnderThisFieldForThisSeason(missingField, row)\n",
    "        # we round the median values, make them of integer type and then impute those values in the dataframe\n",
    "        train_df.at[rowIx, missingField] = int(np.round(np.nanmedian(list(valuesForCurrentSeason)), 0))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "id": "ce7e3204-92d9-4fa5-9d29-3ea171a1e171",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>AC</th>\n",
       "      <th>AF</th>\n",
       "      <th>AR</th>\n",
       "      <th>AS</th>\n",
       "      <th>AST</th>\n",
       "      <th>AY</th>\n",
       "      <th>AwayTeam</th>\n",
       "      <th>Date</th>\n",
       "      <th>FTR</th>\n",
       "      <th>HC</th>\n",
       "      <th>HF</th>\n",
       "      <th>HR</th>\n",
       "      <th>HS</th>\n",
       "      <th>HST</th>\n",
       "      <th>HTAG</th>\n",
       "      <th>HTHG</th>\n",
       "      <th>HY</th>\n",
       "      <th>HomeTeam</th>\n",
       "      <th>league</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [AC, AF, AR, AS, AST, AY, AwayTeam, Date, FTR, HC, HF, HR, HS, HST, HTAG, HTHG, HY, HomeTeam, league]\n",
       "Index: []"
      ]
     },
     "execution_count": 75,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "train_df[train_df.isnull().any(axis=1)] # we see no null values now"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "id": "0f4b8324-1d94-4c9a-842f-60e247ff3254",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>AC</th>\n",
       "      <th>AF</th>\n",
       "      <th>AR</th>\n",
       "      <th>AS</th>\n",
       "      <th>AST</th>\n",
       "      <th>AY</th>\n",
       "      <th>AwayTeam</th>\n",
       "      <th>Date</th>\n",
       "      <th>FTR</th>\n",
       "      <th>HC</th>\n",
       "      <th>HF</th>\n",
       "      <th>HR</th>\n",
       "      <th>HS</th>\n",
       "      <th>HST</th>\n",
       "      <th>HTAG</th>\n",
       "      <th>HTHG</th>\n",
       "      <th>HY</th>\n",
       "      <th>HomeTeam</th>\n",
       "      <th>league</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [AC, AF, AR, AS, AST, AY, AwayTeam, Date, FTR, HC, HF, HR, HS, HST, HTAG, HTHG, HY, HomeTeam, league]\n",
       "Index: []"
      ]
     },
     "execution_count": 76,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "test_df[test_df.isnull().any(axis=1)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 77,
   "id": "ebcf166d-3831-4ff3-a80f-b1bd2bdf7ed2",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "League :: B1\n",
      "[('01', 57), ('02', 58), ('03', 42), ('04', 55), ('07', 22), ('08', 50), ('09', 45), ('10', 67), ('11', 30), ('12', 37)]\n",
      "=======\n",
      "\n",
      "League :: D1\n",
      "[('01', 39), ('02', 57), ('03', 52), ('04', 55), ('05', 25), ('08', 40), ('09', 51), ('10', 54), ('11', 44), ('12', 27)]\n",
      "=======\n",
      "\n",
      "League :: E0\n",
      "[('01', 38), ('02', 72), ('03', 53), ('04', 76), ('05', 38), ('08', 64), ('09', 45), ('10', 74), ('11', 42), ('12', 68)]\n",
      "=======\n",
      "\n",
      "League :: E1\n",
      "[('01', 63), ('02', 110), ('03', 90), ('04', 108), ('05', 12), ('07', 5), ('08', 96), ('09', 81), ('10', 119), ('11', 79), ('12', 116)]\n",
      "=======\n",
      "\n",
      "League :: E2\n",
      "[('01', 85), ('02', 116), ('03', 100), ('04', 118), ('05', 12), ('07', 9), ('08', 97), ('09', 81), ('10', 125), ('11', 58), ('12', 86)]\n",
      "=======\n",
      "\n",
      "League :: E3\n",
      "[('01', 78), ('02', 111), ('03', 101), ('04', 121), ('05', 8), ('07', 9), ('08', 97), ('09', 87), ('10', 110), ('11', 61), ('12', 83)]\n",
      "=======\n",
      "\n",
      "League :: F1\n",
      "[('01', 45), ('02', 70), ('03', 59), ('04', 62), ('05', 39), ('06', 10), ('08', 64), ('09', 52), ('10', 59), ('11', 39), ('12', 42)]\n",
      "=======\n",
      "\n",
      "League :: F2\n",
      "[('01', 58), ('02', 61), ('03', 46), ('04', 63), ('05', 31), ('06', 9), ('07', 8), ('08', 79), ('09', 66), ('10', 56), ('11', 33), ('12', 43)]\n",
      "=======\n",
      "\n",
      "League :: G1\n",
      "[('01', 58), ('02', 52), ('03', 31), ('04', 49), ('05', 10), ('08', 18), ('09', 44), ('10', 39), ('11', 32), ('12', 28)]\n",
      "=======\n",
      "\n",
      "League :: I1\n",
      "[('01', 70), ('02', 65), ('03', 51), ('04', 70), ('05', 42), ('06', 9), ('08', 47), ('09', 59), ('10', 63), ('11', 53), ('12', 43)]\n",
      "=======\n",
      "\n",
      "League :: N1\n",
      "[('01', 49), ('02', 63), ('03', 51), ('04', 50), ('05', 32), ('08', 44), ('09', 50), ('10', 54), ('11', 37), ('12', 20)]\n",
      "=======\n",
      "\n",
      "League :: SP1\n",
      "[('01', 56), ('02', 69), ('03', 49), ('04', 62), ('05', 42), ('06', 8), ('08', 47), ('09', 60), ('10', 66), ('11', 42), ('12', 44)]\n",
      "=======\n",
      "\n"
     ]
    }
   ],
   "source": [
    "for league in list(np.unique(train_df[\"league\"])):\n",
    "    print(f\"League :: {league}\")\n",
    "    print(sorted(Counter(train_df[(train_df[\"league\"] == league)][\"Date\"].dt.strftime(\"%m\")).items()))\n",
    "    print(\"=======\\n\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "id": "1d73de20-79cc-4d76-ad01-6a8ed3296646",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      ">> B1 matches in summer of 2021 were []\n",
      ">> D1 matches in summer of 2021 were []\n",
      ">> E0 matches in summer of 2021 were []\n",
      ">> E1 matches in summer of 2021 were []\n",
      ">> E2 matches in summer of 2021 were []\n",
      ">> E3 matches in summer of 2021 were []\n",
      ">> F1 matches in summer of 2021 were []\n",
      ">> F2 matches in summer of 2021 were []\n",
      ">> G1 matches in summer of 2021 were []\n",
      ">> I1 matches in summer of 2021 were []\n",
      ">> N1 matches in summer of 2021 were []\n",
      ">> SP1 matches in summer of 2021 were []\n",
      "******************************************************************************************\n",
      ">> B1 matches in summer of 2022 were [('07', 15)]\n",
      ">> D1 matches in summer of 2022 were []\n",
      ">> E0 matches in summer of 2022 were []\n",
      ">> E1 matches in summer of 2022 were [('07', 5)]\n",
      ">> E2 matches in summer of 2022 were [('07', 9)]\n",
      ">> E3 matches in summer of 2022 were [('07', 9)]\n",
      ">> F1 matches in summer of 2022 were []\n",
      ">> F2 matches in summer of 2022 were [('07', 8)]\n",
      ">> G1 matches in summer of 2022 were []\n",
      ">> I1 matches in summer of 2022 were []\n",
      ">> N1 matches in summer of 2022 were []\n",
      ">> SP1 matches in summer of 2022 were []\n",
      "******************************************************************************************\n",
      ">> B1 matches in summer of 2023 were [('07', 7)]\n",
      ">> D1 matches in summer of 2023 were []\n",
      ">> E0 matches in summer of 2023 were []\n",
      ">> E1 matches in summer of 2023 were []\n",
      ">> E2 matches in summer of 2023 were []\n",
      ">> E3 matches in summer of 2023 were []\n",
      ">> F1 matches in summer of 2023 were [('06', 10)]\n",
      ">> F2 matches in summer of 2023 were [('06', 9)]\n",
      ">> G1 matches in summer of 2023 were []\n",
      ">> I1 matches in summer of 2023 were [('06', 9)]\n",
      ">> N1 matches in summer of 2023 were []\n",
      ">> SP1 matches in summer of 2023 were [('06', 8)]\n",
      "******************************************************************************************\n"
     ]
    }
   ],
   "source": [
    "# count the number of matches in the summer months of the year for all the leagues in the train_df\n",
    "# For e.g. ('06', 10) means 10 matches in June of that year\n",
    "\n",
    "for year in range(2021, 2023+1): # since train_df is from calendar year 2009 to 2017\n",
    "    for league in list(np.unique(train_df[\"league\"])):\n",
    "        counts = sorted(Counter(train_df[  (train_df[\"league\"] == league) & (train_df[\"Date\"].dt.year == year) & \\\n",
    "                                            ((train_df[\"Date\"].dt.month == 6) | (train_df[\"Date\"].dt.month == 7)) # summer months\n",
    "                                        ][\"Date\"].dt.strftime(\"%m\")).items() )\n",
    "\n",
    "        print(f\">> {league} matches in summer of {str(year)} were {counts}\")\n",
    "    print(\"***\"*30)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "id": "27a9484e-9138-42ec-bf86-97fbe8d7c577",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>AC</th>\n",
       "      <th>AF</th>\n",
       "      <th>AR</th>\n",
       "      <th>AS</th>\n",
       "      <th>AST</th>\n",
       "      <th>AY</th>\n",
       "      <th>AwayTeam</th>\n",
       "      <th>Date</th>\n",
       "      <th>FTR</th>\n",
       "      <th>HC</th>\n",
       "      <th>HF</th>\n",
       "      <th>HR</th>\n",
       "      <th>HS</th>\n",
       "      <th>HST</th>\n",
       "      <th>HTAG</th>\n",
       "      <th>HTHG</th>\n",
       "      <th>HY</th>\n",
       "      <th>HomeTeam</th>\n",
       "      <th>league</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [AC, AF, AR, AS, AST, AY, AwayTeam, Date, FTR, HC, HF, HR, HS, HST, HTAG, HTHG, HY, HomeTeam, league]\n",
       "Index: []"
      ]
     },
     "execution_count": 79,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# La-Liga matches in June and July 2014\n",
    "train_df[(train_df[\"league\"] == \"la-liga\") & (train_df[\"Date\"].dt.year == 2013) & ((train_df[\"Date\"].dt.month == 6) | (train_df[\"Date\"].dt.month == 7))]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 80,
   "id": "1ffdd9e7-aee2-43e7-8af9-e8fc6a4b9f54",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2022-23 B1 season was between 22 July 2022 and 23 April 2023 \n",
      "2022-23 D1 season was between 05 August 2022 and 27 May 2023 \n",
      "2022-23 E0 season was between 05 August 2022 and 28 May 2023 \n",
      "2022-23 E1 season was between 29 July 2022 and 08 May 2023 \n",
      "2022-23 E2 season was between 30 July 2022 and 07 May 2023 \n",
      "2022-23 E3 season was between 30 July 2022 and 08 May 2023 \n",
      "2022-23 F1 season was between 05 August 2022 and 03 June 2023 ------    ran beyond May\n",
      "2022-23 F2 season was between 30 July 2022 and 02 June 2023 ------    ran beyond May\n",
      "2022-23 G1 season was between 19 August 2022 and 14 May 2023 \n",
      "2022-23 I1 season was between 13 August 2022 and 04 June 2023 ------    ran beyond May\n",
      "2022-23 N1 season was between 05 August 2022 and 28 May 2023 \n",
      "2022-23 SP1 season was between 12 August 2022 and 04 June 2023 ------    ran beyond May\n",
      "=========\n",
      "2023-24 B1 season was between 28 July 2023 and 21 April 2024 \n",
      "2023-24 D1 season was between 18 August 2023 and 21 April 2024 \n",
      "2023-24 E0 season was between 11 August 2023 and 21 April 2024 \n",
      "2023-24 E1 season was between 04 August 2023 and 22 April 2024 \n",
      "2023-24 E2 season was between 05 August 2023 and 20 April 2024 \n",
      "2023-24 E3 season was between 05 August 2023 and 20 April 2024 \n",
      "2023-24 F1 season was between 11 August 2023 and 21 April 2024 \n",
      "2023-24 F2 season was between 05 August 2023 and 20 April 2024 \n",
      "2023-24 G1 season was between 18 August 2023 and 21 April 2024 \n",
      "2023-24 I1 season was between 19 August 2023 and 22 April 2024 \n",
      "2023-24 N1 season was between 11 August 2023 and 14 April 2024 \n",
      "2023-24 SP1 season was between 11 August 2023 and 22 April 2024 \n",
      "=========\n"
     ]
    }
   ],
   "source": [
    "# since train_df is from calendar year 2009 to 2017\n",
    "# therefore, first season is 2008-09; last season is 2017-18\n",
    "\n",
    "for year in range(2022, 2023+1):\n",
    "    season = f\"{str(year)}-{str(year+1)[-2:]}\"\n",
    "    seasonId = str(year)[-2:] + str(year+1)[-2:]\n",
    "    for leagueName in list(np.unique(train_df[\"league\"])):\n",
    "        seasonId = str(year)[-2:] + str(year+1)[-2:]\n",
    "        currSeasonCurrLeagueDF = pd.read_csv(os.path.join(\"downloaded_data\", f\"{leagueName}_{seasonId}.csv\")).dropna(how='all')\n",
    "        try:\n",
    "            firstDayOfTheSeason = min(pd.to_datetime(currSeasonCurrLeagueDF[\"Date\"], format=\"%d/%m/%y\")) # earlier dates are \"lesser\"\n",
    "            lastDayOfTheSeason = max(pd.to_datetime(currSeasonCurrLeagueDF[\"Date\"], format=\"%d/%m/%y\")) # later dates are \"greater\"\n",
    "        except ValueError as err:\n",
    "            firstDayOfTheSeason = min(pd.to_datetime(currSeasonCurrLeagueDF[\"Date\"], format=\"%d/%m/%Y\")) # earlier dates are \"lesser\"\n",
    "            lastDayOfTheSeason = max(pd.to_datetime(currSeasonCurrLeagueDF[\"Date\"], format=\"%d/%m/%Y\")) # later dates are \"greater\"\n",
    "        \n",
    "        # pretty print the start and end date of the season\n",
    "        firstMatchDateStr, lastMatchDateStr = firstDayOfTheSeason.strftime('%d %B %Y'), lastDayOfTheSeason.strftime('%d %B %Y')\n",
    "        \n",
    "        # catch seasons which end beyond May\n",
    "        seasonRunsBeyondMay = \"------    ran beyond May\" if lastDayOfTheSeason.year == (year+1) and lastDayOfTheSeason.month > 5 else \"\"\n",
    "        \n",
    "        print(f\"{season} {leagueName} season was between {firstMatchDateStr} and {lastMatchDateStr} {seasonRunsBeyondMay}\")\n",
    "    print(\"=========\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 81,
   "id": "6ff2ad51-a2f4-4210-93ac-04e6a62574e7",
   "metadata": {},
   "outputs": [],
   "source": [
    "if not os.path.exists('imputed_data'): \n",
    "    os.mkdir(\"imputed_data\") # save imputed data in a new directory\n",
    "    \n",
    "train_df.to_csv(os.path.join(\"imputed_data\", 'train.csv'), index=False)\n",
    "test_df.to_csv(os.path.join(\"imputed_data\", 'test-3.csv'), index=False)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.11.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
